﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace DAL
{
    public class SanPhamDAL
    {
         private SqlConnection cn = null;
         public SanPhamDAL()
        {
            string stringConnection = ConfigurationSettings.AppSettings["connectionstring"].ToString();
            cn = new SqlConnection(stringConnection);
            cn.Open();
        }

        public void INSERT(SanPham D)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_SANPHAM_Insert";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;

            cm.Parameters.AddWithValue("@TenSanPham", D.TenSanPham);
            cm.Parameters.AddWithValue("@CPU", D.CPU);
            cm.Parameters.AddWithValue("@RAM", D.RAM);
            cm.Parameters.AddWithValue("@HDD", D.HDD);
            cm.Parameters.AddWithValue("@CardManHinh", D.CardManHinh);
            cm.Parameters.AddWithValue("@CD_DVD", D.CD_DVD);
            cm.Parameters.AddWithValue("@ManHinh", D.ManHinh);
            cm.Parameters.AddWithValue("@Mang", D.Mang);
            cm.Parameters.AddWithValue("@TichHop", D.TichHop);
            cm.Parameters.AddWithValue("@TrongLuong", D.TrongLuong);
            cm.Parameters.AddWithValue("@Pin", D.Pin);
            cm.Parameters.AddWithValue("@HeDieuHanh", D.HeDieuHanh);
            cm.Parameters.AddWithValue("@ThoiGianBaoHanh", D.ThoiGianBaoHanh);
            cm.Parameters.AddWithValue("@KhuyenMai", D.KhuyenMai);
            cm.Parameters.AddWithValue("@Gia", D.Gia);
            cm.Parameters.AddWithValue("@MaNhaSanXuat", D.MaNhaSanXuat);
            cm.Parameters.AddWithValue("@MaLoai", D.MaLoai);

            cm.ExecuteNonQuery();
        }

        public void UPDATE(SanPham D)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_SANPHAM_Update";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;

            cm.Parameters.AddWithValue("@MaSanPham", D.MaSanPham);
            cm.Parameters.AddWithValue("@TenSanPham", D.TenSanPham);
            cm.Parameters.AddWithValue("@CPU", D.CPU);
            cm.Parameters.AddWithValue("@RAM", D.RAM);
            cm.Parameters.AddWithValue("@HDD", D.HDD);
            cm.Parameters.AddWithValue("@CardManHinh", D.CardManHinh);
            cm.Parameters.AddWithValue("@CD_DVD", D.CD_DVD);
            cm.Parameters.AddWithValue("@ManHinh", D.ManHinh);
            cm.Parameters.AddWithValue("@Mang", D.Mang);
            cm.Parameters.AddWithValue("@TichHop", D.TichHop);
            cm.Parameters.AddWithValue("@TrongLuong", D.TrongLuong);
            cm.Parameters.AddWithValue("@Pin", D.Pin);
            cm.Parameters.AddWithValue("@HeDieuHanh", D.HeDieuHanh);
            cm.Parameters.AddWithValue("@ThoiGianBaoHanh", D.ThoiGianBaoHanh);
            cm.Parameters.AddWithValue("@KhuyenMai", D.KhuyenMai);
            cm.Parameters.AddWithValue("@Gia", D.Gia);
            cm.Parameters.AddWithValue("@MaNhaSanXuat", D.MaNhaSanXuat);
            cm.Parameters.AddWithValue("@MaLoai", D.MaLoai);

            cm.ExecuteNonQuery();
        }

        public void DELETE(int iD)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_SANPHAM_Delete";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@MaSanPham", iD);
            cm.ExecuteNonQuery();
        }
        public int MaSanPhamCuoiCung(string tendangnhap_nhanvien)
        {
            SqlCommand laymasp = new SqlCommand("select MaSanPham from SANPHAM where TenDangNhap_NhanVien=@tendangnhap and MaSanPham = (select MAX(MaSanPham) from SANPHAM)", cn);
            laymasp.Parameters.AddWithValue("@tendangnhap", tendangnhap_nhanvien);
            return int.Parse(laymasp.ExecuteScalar().ToString());
        }
        public SanPhamCollection GET(string Where, int PageIndex, int PageSize, string OrderBy, string OrderDirection, out int TotalRecords)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_SANPHAM_Get";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@Where", Where);
            cm.Parameters.AddWithValue("@PageIndex", PageIndex);
            cm.Parameters.AddWithValue("@PageSize", PageSize);
            cm.Parameters.AddWithValue("@OrderBy", OrderBy);
            cm.Parameters.AddWithValue("@OrderDirection", OrderDirection);
            SqlParameter param = new SqlParameter("@TotalRecords", DbType.Int32);
            param.Direction = ParameterDirection.Output;
            cm.Parameters.Add(param);
            SqlDataAdapter da = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            SanPhamCollection SanPhamCollection = new SanPhamCollection();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                SanPham D = new SanPham();
                D.MaSanPham = int.Parse(ds.Tables[0].Rows[i]["MaSanPham"].ToString());
                D.TenSanPham = ds.Tables[0].Rows[i]["TenSanPham"].ToString();
                D.CPU = ds.Tables[0].Rows[i]["CPU"].ToString();
                D.RAM = ds.Tables[0].Rows[i]["RAM"].ToString();
                D.HDD = ds.Tables[0].Rows[i]["HDD"].ToString();
                D.CardManHinh = ds.Tables[0].Rows[i]["CardManHinh"].ToString();
                D.CD_DVD = ds.Tables[0].Rows[i]["CD_DVD"].ToString();
                D.ManHinh = ds.Tables[0].Rows[i]["ManHinh"].ToString();
                D.Mang = ds.Tables[0].Rows[i]["Mang"].ToString();
                D.TichHop = ds.Tables[0].Rows[i]["TichHop"].ToString();
                D.TrongLuong = ds.Tables[0].Rows[i]["TrongLuong"].ToString();
                D.Pin = ds.Tables[0].Rows[i]["Pin"].ToString();
                D.HeDieuHanh = ds.Tables[0].Rows[i]["HeDieuHanh"].ToString();
                D.ThoiGianBaoHanh = ds.Tables[0].Rows[i]["ThoiGianBaoHanh"].ToString();
                D.KhuyenMai = ds.Tables[0].Rows[i]["KhuyenMai"].ToString();
                D.Gia = int.Parse(ds.Tables[0].Rows[i]["Gia"].ToString());
                D.MaNhaSanXuat = int.Parse(ds.Tables[0].Rows[i]["MaNhaSanXuat"].ToString());
                D.MaLoai = int.Parse(ds.Tables[0].Rows[i]["MaLoai"].ToString());
                SanPhamCollection.Add(D);
            }
            TotalRecords = int.Parse(cm.Parameters["@TotalRecords"].Value.ToString());
            return SanPhamCollection;
        }
    }
}
